﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.Data;
using System.IO;
using System.Data.OleDb;

namespace SupermarketReport
{
    class Report
    {
        static string[] xlsFiles;
        static List<Quad> quads;

        public static void CreateConnection()
        {
            quads = new List<Quad>();

            for (int i = 0; i < xlsFiles.Length; i++)
            {
                OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + xlsFiles[i] + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\";");
                con.Open();
                try
                {
                    DataSet myDataSet = new DataSet();
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + "Sales" + "$]", con);
                    myCommand.Fill(myDataSet);
                    con.Close();

                    DataRowCollection allRows = myDataSet.Tables[0].Rows;
                    DataRow headRow = allRows[0];
                    Object[] firstRowcells = headRow.ItemArray;
                    string marketname = firstRowcells[0].ToString();
                    Console.WriteLine(marketname);

                    for (int k = 2; k < allRows.Count; k++)
                    {
                        DataRow myDataRow = allRows[k];
                        Object[] cells = myDataRow.ItemArray;

                        string prID = cells[0].ToString();
                        //Console.WriteLine(prID);
                        string quan = cells[1].ToString();
                        // Console.WriteLine(quan);
                        string price = cells[2].ToString();
                        // Console.WriteLine(price);

                        if (quan == "")
                        {
                            break;
                        }
                        quads.Add(new Quad(int.Parse(prID), int.Parse(quan), double.Parse(price), marketname));
                    }
                }
                catch (Exception ex)
                {
                    //Handle
                }
                finally
                {
                    con.Close();
                }
            }
        }

        public static void GetFiles()
        {
            DirectoryInfo dInfo = new DirectoryInfo("Reports");
            int numOfFiles = dInfo.GetFiles().Length;
            xlsFiles = new String[numOfFiles];
            int k = 0;

            foreach (var item in dInfo.GetFiles())
            {
                xlsFiles[k++] = item.FullName;
            }
        }

        static void AddRowToDataTable(PdfPTable PdfTable, string name, string quantity, string price, string location, string sum)
        {
            Font font8 = FontFactory.GetFont("ARIAL", 7);
            PdfPCell PdfCell = new PdfPCell();
            PdfCell = new PdfPCell(new Phrase(new Chunk(name, font8)));
            PdfCell.BackgroundColor = BaseColor.GREEN;
            PdfTable.AddCell(PdfCell);
            PdfCell = new PdfPCell(new Phrase(new Chunk(quantity, font8)));
            PdfCell.BackgroundColor = BaseColor.GREEN;
            PdfTable.AddCell(PdfCell);
            PdfCell = new PdfPCell(new Phrase(new Chunk(price, font8)));
            PdfCell.BackgroundColor = BaseColor.GREEN;
            PdfTable.AddCell(PdfCell);
            PdfCell = new PdfPCell(new Phrase(new Chunk(location, font8)));
            PdfCell.BackgroundColor = BaseColor.GREEN;
            PdfTable.AddCell(PdfCell);
            PdfCell = new PdfPCell(new Phrase(new Chunk(sum, font8)));
            PdfCell.BackgroundColor = BaseColor.GREEN;
            PdfTable.AddCell(PdfCell);
        }

        public static void ExportDataToPDFTable(List<Quad> quads, MySqlConnection mySQLCon)
        {
            Document doc = new Document(iTextSharp.text.PageSize.LETTER, 10, 10, 42, 35);
            try
            {
                string pdfFilePath = "myPdf1.pdf";
                PdfWriter.GetInstance(doc, new FileStream(pdfFilePath, FileMode.Create));
                doc.Open();
                DataTable dt = CreateDataTable();
                if (dt != null)
                {
                    PdfPTable PdfTable = new PdfPTable(dt.Columns.Count);
                    AddRowToDataTable(PdfTable, "Name", "Quantity", "Unit Price", "Location", "Sum");

                    double total = 0;
                    foreach (Quad quad in quads)
                    {
                        string strSQL = "SELECT p.Name AS Product, CONCAT('" + quad.Quantity + " ', m.name) AS Quantity, '" + quad.Unitprice +
                            "', '" + quad.MarketName + "' AS Location, (" + quad.Quantity + " * p.Price) AS Total FROM products p JOIN Vendors v " +
                            "ON p.VendorID = v.VendorID JOIN Measures m ON m.MeasureID = p.MeasureID WHERE p.ProductID = " + quad.ProductID;

                        MySqlCommand mysqlCmd = new MySqlCommand(strSQL, mySQLCon);
                        MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();

                        while (mysqlReader.Read())
                        {
                            string product = mysqlReader.GetString(0);
                            string quantity = mysqlReader.GetString(1);
                            string price = mysqlReader.GetDouble(2).ToString();
                            string location = mysqlReader.GetString(3);
                            total += mysqlReader.GetDouble(4);
                            string totalStr = mysqlReader.GetDouble(4).ToString();

                            AddRowToDataTable(PdfTable, product, quantity, price, location, totalStr);
                        }
                        mysqlReader.Close();
                    }
                    AddRowToDataTable(PdfTable, "", "", "", "Total", total.ToString());
                    doc.Add(PdfTable);
                }
            }
            catch (DocumentException docEx)
            {
                //handle pdf document exception if any
            }
            catch (IOException ioEx)
            {
                // handle IO exception
            }
            catch (Exception ex)
            {
                // ahndle other exception if occurs
            }
            finally
            {
                doc.Close();
            }
        }

        public static DataTable CreateDataTable()
        {
            DataTable dataTable = new DataTable("MyDataTable");
            DataColumn dataColumn_Name = new DataColumn("Product", typeof(string));
            dataTable.Columns.Add(dataColumn_Name);
            DataColumn dataColumn_Quantity = new DataColumn("Quantity", typeof(string));
            dataTable.Columns.Add(dataColumn_Quantity);
            DataColumn dataColumn_UnitPrice = new DataColumn("Unit Price", typeof(double));
            dataTable.Columns.Add(dataColumn_UnitPrice);
            DataColumn dataColumn_Location = new DataColumn("Location", typeof(string));
            dataTable.Columns.Add(dataColumn_Location);
            DataColumn dataColumn_Sum = new DataColumn("Sum", typeof(string));
            dataTable.Columns.Add(dataColumn_Sum);
            dataTable.AcceptChanges();
            return dataTable;
        }

        static void Main()
        {
            string strProvider = "Data Source=" + "localhost" + ";Database=" + "Supermarket" + ";User ID=" + "root" + ";Password=" + "root";
            MySqlConnection mysqlCon = new MySqlConnection(strProvider);

            mysqlCon.Open();
            GetFiles();
            CreateConnection();
            ExportDataToPDFTable(quads, mysqlCon);
            mysqlCon.Close();
        }
    }
}

